<?php

class Pawns_model extends Model {
	
	var $pawn_id;
	var $pawn_control_number;
	var $customer_id;				// lookup field
	var $pawn_date;
	var $pawn_time;
	var $pawn_transaction_type_id;	// lookup field
	var $pawn_amount_financed;
	var $pawn_note;
	var $pawn_status_id;			// lookup field
	var $monthly_interest;
	
	// calculated fields
	var $finance_charge;			// monthly interest charge, based on $monthly_interest and $pawn_amount_financed
	var $maturity_date;				// one month after the $pawn_date
	var $total_payments;			// amount required to redeem pledged property on maturity date
	var $apr;						// annual percentage rate
	
	var $accrued_interest;     // How much interest is owed
   var $paid_interest;        // How much interest has been paid    
   var $paid_principal;       // How much principal has been paid
   var $months_owed;          // Number of months owed
    
   	
	// Constant not stored in database
	var $dept_number;
	
	function Pawns_model()
	{
		parent::Model();
	}
	
	// This function compares an array of data to the table fields, and deletes any items in the data
	// that are not a field in the table
	
	protected function filterInputArray($table, $data, $xss_clean = false, $caps = true){
        $fields = $this->db->list_fields($table);

        foreach ($data as $k => $v) {
                if(in_array($k, $fields) == false){
                        unset($data[$k]);
                } 
				else 
				{
                        if($xss_clean === true) $data[$k] = $this->input->xss_clean($v);
                        if($caps === true) $data[$k] = strtoupper($v); 	// convert to CAPS
                }
        }

        return $data;
	}

	function general()
	{
		// Set up the parameters for the form_helper function that displays the fields 
		// in the pawn add and pawn update view
		$data['fcontrolnumber'] 	= array('name'=>'pawn_control_number','size'=>15 );
		$data['fpawnmonth'] 		   = array('name'=>'pawn_month', 'size'=>2 ); 	                                
      $data['fpawnday'] 			= array('name'=>'pawn_day', 'size'=>2 );                             
		$data['fpawnyear'] 			= array('name'=>'pawn_year','size'=>4 );                             
		$data['fpawntime'] 			= array('name'=>'pawn_time', 'size'=>6 ); 	                                
	    		
		$data['famountfinanced'] 	= array('name'=>'pawn_amount_financed','size'=>6 );     
		$data['fmonthlyinterest'] 	= array('name'=>'monthly_interest', 'size'=>4 ); 
		$data['fnote'] 				= array('name'=>'pawn_note', 'rows'=>2, 'cols'=>50 ); 
		
		// calculated fields
		$data['ffinancecharge'] 	= array('name'=>'finance_charge', 'size'=>4 ); 
      $data['fmaturitymonth'] 	= array('name'=>'maturity_month', 'size'=>2 );  
      $data['fmaturityday'] 		= array('name'=>'maturity_day', 'size'=>2 ); 
		$data['fmaturityyear'] 		= array('name'=>'maturity_year', 'size'=>4 ); 
      //$data['fcustomername'] 	= array('name'=>'customer_name','size'=>25 );
	    
	    
	    // Get the options to populate the dropdown lookup fields
	    $fCustomerOptions = $this->get_Customers();
	    $fTransactionTypeOptions = $this->get_TransactionTypes();
	    $fStatusOptions = $this->get_Status();
	    	    
	    // setup the lookup fields
	    $data['fcustomerid']		= array('name'=>'customer_id', 'options' => $fCustomerOptions, 'select' =>'');
	    $data['ftransactiontype']	= array('name'=>'pawn_transaction_type_id', 
											'options' => $fTransactionTypeOptions, 'select' =>'');
	    $data['fstatus']			= array('name'=>'pawn_status_id', 'options' => $fStatusOptions, 'select' =>'');
	    
	    
	    // Retrieve the database fields
	    $fields = $this->db->list_fields('pawns');
	    $fields[] = 'pawn_month';
	    $fields[] = 'pawn_day';
	    $fields[] = 'pawn_year';
	    $fields[] = 'maturiry_month';
	    $fields[] = 'maturity_day';
	    $fields[] = 'maturity_year';
	    $fields[] = 'finance_charge';
	    
	    	    
	    // Create an associative array of the table field names as key, and an empty value
	    foreach ($fields as $k => $v)
	    	$empty_table[$v] = '';			
				
		// convert the array to objects and store it in $data['result']
		$data['result'] = parseArrayToObject($empty_table);	
		
		return $data;    	    
	}
	
	function getLastPawnNumber()
	{
		$query = $this->db->query('SELECT MAX(pawn_control_number) FROM pawns');
		
		$row = $query->row_array();
		
		$last_pawn_control_number = $row['MAX(pawn_control_number)'];
		
		return ($last_pawn_control_number + 1);
		
	}
	
	// return the total months
	function getTotalMonths($date1)
	{
	   
      $date2 = date("Y-m-d");
      $total_months = date_diff($date1,$date2);
      
      return $total_months;
   }
	
	function getAccruedInterest($total_months,$finance_charge,$already_paid)
	{
      // How much interest is owed
      // Take the Total Number of months and multiply by the $finance_charge
      // Subtract the sum of payments from the previous calculation
      
      // If we're still in the same month as the pawn date, then charge for 1 month
      if (!$total_months)
         $total_months = 1;
         
      return((int)$total_months * (int)$finance_charge - (int)$already_paid);
   }
	
	function getPaidInterest($pawn_id)
	{
      // query the payments table with the current pawn_id
      // add up the $payment_amount field for the result set
      
      $this->db->select_sum('payment_amount');
      $this->db->where('pawn_id', $pawn_id); 
      $query = $this->db->get('payments');
      
      $result = $query->row_array();
      
      return $result['payment_amount'];
   }
	function getTotalDue()
	{
      // Initial pawn amount + accrued interest - paid interest - principal paid
   }
	
	function get() {
		
		// If any of the following properties are set before pawns_model->get() is called from the controller
		//  then we will include a where statement for each of the properties that have been set.
        if ($this->pawn_id) {
			$this->db->where("pawn_id", $this->pawn_id);
		}
		if ($this->customer_id) {
			$this->db->where("pawns.customer_id", $this->customer_id);
        }
		if ($this->pawn_control_number) {
            $this->db->where("pawn_control_number", $this->pawn_control_number);
        }
        if ($this->pawn_status_id) {
            $this->db->where("pawn_status_id", $this->pawn_status_id);
        }

		// Main select queries                
		$this->db->select('*');
		$this->db->select('customers.last_name, customers.first_name, customers.middle');
		$this->db->select('pawn_transaction_types.transaction_description');
		$this->db->select('pawn_status.pawn_status');

		$this->db->from('pawns');
		$this->db->join('customers','pawns.customer_id = customers.customer_id','left');
		$this->db->join('pawn_transaction_types','pawns.pawn_transaction_type_id = 
							pawn_transaction_types.pawn_transaction_type_id','left');
		$this->db->join('pawn_status','pawns.pawn_status_id = pawn_status.pawn_status_id','left');
				
		// We will display our results in order by last name and then first name.
        // $this->db->orderby("last_name, first_name");

		$query = $this->db->get();
		
		// Store the database query in an array.
		// The result is a pure array. We need it to be an array in order to manipulate it and 
		// add the calculated fields
		
		// If you set Pawns_model->pawn_id from your controller, then there will only be one row to return.
      if ($this->pawn_id) {
         return ($query->row_array());		// returns an object
      }
      else
      {
         $result = $query->result_array();  
		
   		// Add the calculated fields to the result array
   	   $i = 0;
   	   foreach ($result as $row)
   	   {  
   	      $key1 = "months_owed";
            $key2 = "accrued_interest";
            $key3 = "paid_interest";
            $key4 = "total_due";
            $key5 = "total_months";
            $key6 = "paid_principal";
            
            
            $months_owed = 0;
   	      $total_months = $this->getTotalMonths($result[$i]['pawn_date']);
   	      $accrued_interest = $this->getAccruedInterest($total_months,$result[$i]['finance_charge'],0);
   	      $paid_interest = $this->getPaidInterest($result[$i]['pawn_id']);
   	      $total_due = $result[$i]['pawn_amount_financed'] + $accrued_interest - $paid_interest;
   	      
   	      
   	      $result[$i][$key1] = $months_owed;
   	      $result[$i][$key2] = $accrued_interest;
   	      $result[$i][$key3] = $paid_interest;
   	      $result[$i][$key4] = $total_due;
   	      $result[$i][$key5] = $total_months;
   	      $result[$i][$key6] = "none";
   	      
            $i++;           
         }
      return $result;
      }
                     
      
   }
	
	function save($data)
	{
		// Allows you to add a new pawn as well as update an existing pawn
		
		$update = FALSE;
				
		$db_array = $this->filterInputArray('pawns', $data);

		if ($db_array['pawn_id'] != '')		// If pawn_id field is not blank, then it's an update
		{
			$update = TRUE;
			$pawn_id = $db_array['pawn_id'];
		}
		
		unset($db_array['pawn_id']);

        if ($update) {
            $this->db->where("pawn_id", $this->pawn_id);
            $this->db->update("pawns", $db_array);
        }
        else {
            $this->db->insert("pawns", $db_array);
        }
        
    }
	
	function delete($id)
	{
		// function deletes a pawn record based on the Id value
		// As long as Pawns_model->pawn_id was set in the controller, we will delete the record.
        
        $this->pawn_id = $id;
        
		if ($this->pawn_id) {
            $this->db->where("pawn_id", $this->pawn_id);
            $this->db->delete("pawns");
        }
		
	}
	
	// return the last inserted record id
	function lastInsertedId()
	{
		$this->db->select('LAST_INSERT_ID()');
		$query = $this->db->get();
		
		$result = $query->row();
		foreach ($result as $k => $v)
		{
			return $result->$k;
		}
	}
	
	
	
	// Lookup Functions
	
	function get_Customers() {
		
		//$this->db->select('customer_id,concat(last_name,first_name,middle_initial)');
		$this->db->select('customer_id,last_name,first_name,middle');
		$this->db->orderby("last_name, first_name");
		$query = $this->db->get('customers');
		if ($query->num_rows() > 0) {
			
			$query_result = $query->result_array();
			
			foreach ($query_result as $row)
			{
				$id = $row['customer_id'];
				$value = $row['last_name'].', '.$row['first_name'].' '.$row['middle'];
				$options_array[$id] = $value;
			}
			return $options_array;
		}
		else
			return FALSE;
	}

	function get_TransactionTypes() {
			
		$query = $this->db->get('pawn_transaction_types');
		if ($query->num_rows() > 0) {		
						
			$query_result = $query->result_array();
			
			foreach ($query_result as $row)
			{
				$id = $row['pawn_transaction_type_id'];
				$value = $row['transaction_description'];
				$options_array[$id] = $value;
			}
			return $options_array;
		}
		else
			return FALSE;
	}
	
	function get_Status() {
			
		$query = $this->db->get('pawn_status');
		if ($query->num_rows() > 0) {
			
			$query_result = $query->result_array();
						
			foreach ($query_result as $row)
			{
				$id = $row['pawn_status_id'];
				$value = $row['pawn_status'];
				$options_array[$id] = $value;
			}
				
			return $options_array;
		}
		else
			return FALSE;
	}
}

?>